# -*- coding:utf-8 -*-
import calendar

from project_zczh.project_1.data_gangcai import now_time
from . import project
from flask import render_template
from project_zczh.tool.common import login_check
from pyecharts import Line
import MySQLdb
import datetime
from decimal import Decimal

mark={
    'complex':'P.C32.5R',
    'ordinary':'P.O42.5'
}

type_shuini={
    'bag':'袋装价,袋装',
    'bulk':'散装价,散装'
}


@project.route('/shuini/table/<string:xinhao>/<string:guige>')
@login_check
def shuini_view(xinhao,guige):
    key=mark[xinhao]
    guige=type_shuini[guige]
    global db
    db = MySQLdb.connect(
        port=3306,
        host='127.0.0.1',
        db='zczh_data',
        user='root',
        passwd='mysql',
        charset='utf8'
    )
    global cur
    cur = db.cursor()

    now_time_point = datetime.datetime.now()
    year = now_time_point.year
    month = now_time_point.month
    date = now_time_point.day
    data_tuple = calendar.monthrange(year, month)

    if date == data_tuple[1]:
        data_prict_2017, data_prict_2018=get_data_month(key, guige)
        data_time, prices=table_data_month(data_prict_2017, data_prict_2018, key, guige)
        bar=bar_month(data_prict_2017, data_prict_2018,key,guige)

    else:
        data_time, prices=table_data(key=key,guige=guige)
        table_title, rows_data=get_data(key=key,guige=guige)
        bar=bar_(table_title=table_title,rows_data=rows_data,key=key)

    return render_template('shuini.html',
                           key=key,
                           guige=guige[:3],
                           data_time=data_time,
                           prices=prices,
                           myechart=bar.render_embed())






def table_data(key,guige):
    # area 地区
    # mark 型号
    # type 散装 袋装
    # price 价格
    # pub_time 时间
    now, delta_now = now_time(7)
    sql = """SELECT mark,area,round(AVG(price),2) as '平均价格',DATE_FORMAT(pub_time ,%s)
                   FROM shuini_data
                   WHERE pub_time <=%s AND pub_time >=%s and mark=%s and type in (%s,%s)
                   GROUP BY area,mark,DATE_FORMAT(pub_time,%s);"""
    cur.execute(sql, ["%Y-%m-%d", now, delta_now, key,guige.split(',')[0],guige.split(',')[1],"%Y-%m-%d"])
    data = cur.fetchall()
    updata_time=list()
    data_time = ['大区名称','型号','规格']
    for i in data:
        updata_time.append(str(i[3]))
    updata_time=list(set(updata_time))
    updata_time.sort()
    data_time+=updata_time
    prices=list()
    area = ['上海','东北', '华东', '华中', '华北', '华南', '西北', '西南']
    area_list=[[addr,key,guige[:3]] for addr in area]
    for k in data:
        for i in area_list:
            if k[1] == i[0]:
                i.append(float(Decimal(k[2]).quantize(Decimal('0.00'))))
    check_data=list()
    for i in area_list:
        check_data.append(len(i))
    for i in area_list:
        if len(i)==max(check_data):
            prices.append(i)
    #数据跟　新显示小红点　小红点
    return data_time,prices

def get_data(key,guige):
    now, delta_now = now_time(30)
    sql = """SELECT mark,area,round(AVG(price),2) as '平均价格',DATE_FORMAT(pub_time ,%s)
                   FROM shuini_data
                   WHERE pub_time <=%s AND pub_time >=%s and mark=%s and type in (%s,%s)
                   GROUP BY area,mark,DATE_FORMAT(pub_time,%s);"""
    cur.execute(sql, ["%Y-%m-%d", now, delta_now, key,guige.split(',')[0],guige.split(',')[1],"%Y-%m-%d"])
    data = cur.fetchall()
    updata_time=list()
    data_time = ['大区名称','型号','规格']
    for i in data:
        updata_time.append(str(i[3]))
    updata_time=list(set(updata_time))
    updata_time.sort()
    data_time+=updata_time
    prices=list()
    area = ['上海','东北', '华东', '华中', '华北', '华南', '西北', '西南']
    area_list=[[addr,key,guige[:4]] for addr in area]
    for k in data:
        for i in area_list:
            if k[1] == i[0]:
                i.append(float(Decimal(k[2]).quantize(Decimal('0.00'))))
    check_data=list()
    for i in area_list:
        check_data.append(len(i))
    for i in area_list:
        if len(i)==max(check_data):
            prices.append(i)
    #数据跟　新显示小红点　小红点
    return data_time,prices

def bar_(table_title, rows_data,key):
    attr = table_title[3:]
    data_price =[]
    for i in rows_data:
        data_price .append(i[3:])

    import numpy as np
    ndarray=np.array(data_price)
    a=np.mean(ndarray,axis=0)
    data_price=list(a)

    b=list()
    for i in data_price:
        b.append(float(Decimal(i).quantize(Decimal('0.0'))))
    data_price=b
    bar = Line("全国-%s"%'水泥')
    bar.width=1100
    bar.add("30日走势-%s"%key,
            attr,
            data_price,
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=True,
            is_label_show=True,
            mark_point=['max'])
    return bar


def bar_month(data_prict_2017, data_prict_2018,key,guige):
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    attr = ['{}月份'.format(i) for i in range(1,13)]
    bar = Line("全国-%s-%s" % (key,guige))
    bar.width = 1100
    bar.add("%s年月走势" % cur_year,
            attr,
            data_prict_2018,
            # mark_line=["average"],
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=False,
            is_label_show=True,
            # mark_point=['max']
            )

    bar.add("%s年月走势" % befor_year,
            attr,
            data_prict_2017,
            # mark_line=["average"],
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=False,
            is_label_show=False,
            # mark_point=['max']
    )
    return bar

def get_data_month(key,guige):
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    befor_year_1 = str(befor_year) + '-12-31'
    befor_year_2 = str(befor_year) + '-01-01'
    cur_year_1 = str(cur_year) + '-12-31'
    cur_year_2 = str(cur_year) + '-01-01'
    sql = """SELECT mark,area,round(AVG(price),2) as '平均价格',DATE_FORMAT(pub_time ,%s)
                   FROM shuini_data
                   WHERE pub_time <=%s AND pub_time >=%s and mark=%s and type in (%s,%s)
                   GROUP BY mark,DATE_FORMAT(pub_time,%s);"""
    cur.execute(sql, ["%Y-%m", befor_year_1, befor_year_2,key,guige.split(',')[0],guige.split(',')[1],"%Y-%m"])
    data_2017 = cur.fetchall()
    data_prict_2017 = []
    for i in data_2017:
        data_prict_2017.append(float(Decimal(i[2]).quantize(Decimal('0.00'))))
    cur.execute(sql, ["%Y-%m", cur_year_1, cur_year_2,key,guige,"%Y-%m"])
    data_2018 = cur.fetchall()
    data_prict_2018 = []
    for i in data_2018:
        data_prict_2018.append(float(Decimal(i[2]).quantize(Decimal('0.00'))))
    return data_prict_2017, data_prict_2018

def table_data_month(data_prict_2017,data_prict_2018,key,guige):
    data_time=['{}月份'.format(i) for i in range(1,13)]
    table_title=['说明','型号','规格']+data_time
    rows_data=[]
    if len(data_prict_2017)!=len(data_prict_2018):
        a=len(data_prict_2017)-len(data_prict_2018)
        for i in range(a):
            data_prict_2018.append('-')
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    rows_data.append([str(befor_year)+'年平均值',key,guige[:3]]+data_prict_2017)
    rows_data.append([str(cur_year)+'年平均值',key,guige[:3]]+data_prict_2018)
    return table_title,rows_data
